package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.cons.Roles;
import cn.ztydata.teachingsystem.heaven.entity.SpecStatistics;
import cn.ztydata.teachingsystem.heaven.entity.Specialty;
import com.google.common.collect.Maps;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;


/**
 * 专业Dao
 *
 * @author fanruiping
 * @since 2014-11-29
 */
@Repository
public class SpecialtyDao extends BaseDao {
    /**
     * 检测指定系部编码下是否有专业
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public Boolean checkByDeptNum(String deptNum){
        String sql = "SELECT COUNT(id) FROM t_specialty WHERE sDeptNum=:deptNum LIMIT 1";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("deptNum", deptNum);

        log.debug("根据系部编码获取专业条数");

        return namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class) == 1;
    }

    /**
     * 新增专业
     * @param entity 专业实体
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public void add(Specialty entity){
        String sql = "INSERT INTO t_specialty(sDeptNum,sNumber,sName,iLeaderId,sTrainingObjective,sOccupationalDirection,sCertificate,sIntro) " +
                "VALUES(:deptNum,:number,:name,:leaderId,:trainingObjective,:occupationalDirection,:certificate,:intro)";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("deptNum", entity.getDeptNum())
                .addValue("number",entity.getNumber()).addValue("name",entity.getName()).addValue("leaderId",entity.getLeaderId())
                .addValue("trainingObjective",entity.getTrainingObjective()).addValue("occupationalDirection",entity.getOccupationalDirection())
                .addValue("certificate",entity.getCertificate()).addValue("intro", entity.getIntro());

        namedParameterJdbcTemplate.update(sql, params);
    }

    /**
     * 更新专业数据
     * @param entity 专业实体类\
     * @return boolean
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public boolean modify(Specialty entity){
        String sql = "UPDATE t_specialty SET sNumber=:number,sName=:name,sTrainingObjective=:trainingObjective," +
                "sOccupationalDirection=:occupationalDirection,sCertificate=:certificate,sIntro=:intro WHERE id=:id";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("number", entity.getNumber())
                .addValue("name", entity.getName()).addValue("intro", entity.getIntro()).addValue("trainingObjective",entity.getTrainingObjective()).
                        addValue("occupationalDirection",entity.getOccupationalDirection())
        .addValue("certificate",entity.getCertificate()).addValue("id", entity.getId());

        log.debug("update specialty");

        return namedParameterJdbcTemplate.update(sql, params) == 1;
    }

    /**
     * 修改专业负责人
     * @param id 专业编号
     * @param leaderId 负责人编号
     * @return boolean
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public boolean updateLeader(Long id,Long leaderId){
        String sql = "UPDATE t_specialty SET iLeaderId=:leaderId WHERE id=:id";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("leaderId", leaderId).addValue("id", id);

        log.debug("update specialty leader");

        return namedParameterJdbcTemplate.update(sql, params) == 1;
    }

    /**
     * 删除指定专业数据
     * @param id 专业编号
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public int delete(long id){
        String sql = "DELETE FROM t_specialty WHERE id=:id";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("id", id);

        log.debug("delete specialty by id");

        return namedParameterJdbcTemplate.update(sql, params);
    }

    /**
     * 获取指定专业数据
     * @param id 专业编号
     * @return Specialty
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public Specialty getById(long id){
        try {
            String sql = "SELECT id,sDeptNum,sNumber,sName,iLeaderId,sTrainingObjective,sOccupationalDirection,sCertificate,sIntro " +
                    "FROM t_specialty WHERE id=:id";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("id", id);

            log.debug("return specialty detail by id");

            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateDetailMapper());
        }catch(EmptyResultDataAccessException ex) {
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }

    /**
     * 根据专业编码获取专业数据
     * @param number 专业编码
     * @return Specialty
     *
     * @author fanruiping
     * @since 2014-12-01
     */
    public Specialty getByNumber(String number){
        try{
            String sql = "SELECT id,sDeptNum,sNumber,sName,iLeaderId,sIntro FROM t_specialty WHERE sNumber=:number";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("number", number);

            log.debug("return specialty detail by number");

            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }

    /**
     * 根据专业编码和编号获取专业数据
     * @param number 专业编码
     * @param id 专业编号
     * @return Specialty
     *
     * @author fanruiping
     * @since 2014-12-01
     */
    public Specialty getByNumberAndId(String number, long id){
        try{
            String sql = "SELECT id,sDeptNum,sNumber,sName,iLeaderId,sIntro FROM t_specialty WHERE sNumber=:number AND id!=:id LIMIT 1";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("number", number).addValue("id", id);

            log.debug("return specialty detail by number and id");

            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }
    /**
     * 根据系部编码获取专业列表
     * @param deptNums 系部编码集合
     * @param pageNum 页码
     * @param pageSize 每页显示条数
     * @return Page
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public Page<Specialty> getList(List<String> deptNums, int pageNum, int pageSize){
        String where = "";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("pageNum", (pageNum - 1) * pageSize)
                .addValue("pageSize", pageSize);

        if(!deptNums.isEmpty()){
            log.debug("系部编码集合为空:!deptNums.isEmpty()");

            where = " WHERE sDeptNum IN(:deptNums)";

            params.addValue("deptNums", deptNums);
        }

        String totalSql = "SELECT COUNT(*) FROM t_specialty"+where+" LIMIT 1";

        String dataSql = "SELECT * FROM t_specialty"+where+" LIMIT :pageNum,:pageSize";

        int total = namedParameterJdbcTemplate.queryForObject(totalSql, params, Integer.class);

        if(total < 1){
            log.debug("未获取到专业列表数据:total < 1");

            return new Page<>();
        }

        List<Specialty> entities = namedParameterJdbcTemplate.query(dataSql, params, CreateDetailMapper());

        log.debug("return specialty list");

        return new Page<>(total, pageSize, pageNum, entities);
    }

    /**
     * 获取指定系部下的所有专业数据
     * @param deptNum 系部编码
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-01
     */
    public List<Specialty> getByDeptNum(String deptNum){
        String sql = "SELECT id,sNumber,sName,sDeptNum,iLeaderId,sIntro FROM t_specialty WHERE sDeptNum=:deptNum";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("deptNum", deptNum);

        log.debug("return specialty by deptNum");

        return namedParameterJdbcTemplate.query(sql, params, CreateMapper());
    }

    /**
     * 获取指定系部下的指定教师所负责的所有专业数据
     * @param deptNum 系部编码
     * @param userId 专业负责人编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-13
     */
    public List<Specialty> getByDeptNumAndLeader(String deptNum,Long userId){
        String where = " WHERE sDeptNum=:deptNum";

        //判断是否限制专业负责人
        if(0 != userId){
            where += " AND iLeaderId=:userId";
        }

        String sql = "SELECT id,sNumber,sName,sDeptNum,iLeaderId,sIntro FROM t_specialty"+where;

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("deptNum", deptNum).addValue("userId",userId);

        log.debug("return specialty by deptNum");

        return namedParameterJdbcTemplate.query(sql, params, CreateMapper());
    }

    /**
     * 获取系部前n个专业信息
     *
     * @author chenxin
     * @since 2015/1/10
     */
    public List<Specialty> getTopForDept(final String deptNum, final int limit) {
        String sql = "SELECT id,sName FROM t_specialty WHERE sDeptNum=:deptNum LIMIT :limit";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("deptNum", deptNum)
                .addValue("limit", limit);

        log.debug("获取系部前n个专业信息");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<Specialty>() {
            @Override
            public Specialty mapRow(ResultSet rs, int rowNum) throws SQLException {
                Specialty entity = new Specialty();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));

                return entity;
            }
        });
    }

    /**
     * 获取所有专业数据
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-01
     */
    public List<Specialty> getAll(){
        String sql = "SELECT id,sNumber,sName,sDeptNum,iLeaderId,sIntro FROM t_specialty";

        log.debug("return all specialty");

        return namedParameterJdbcTemplate.query(sql, CreateMapper());
    }

    /**
     * 获取专业负责人负责的专业集合
     * @param leaderId 专业负责人
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-10
     */
    public List<Specialty> getByLeaderId(long leaderId){
        String sql = "SELECT id,sNumber,sName,sDeptNum,iLeaderId,sIntro FROM t_specialty WHERE iLeaderId=:leaderId";

        log.debug("return specialty list by leaderId");

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("leaderId", leaderId), CreateMapper());
    }

    /**
     * 批量新增专业
     * @param specialtyList 专业信息集合
     * @return int[]
     *
     * @author cx
     * @since 2014-11-29
     */
    public int[] saveSpecs(final List<Specialty> specialtyList) {
        String sql = "INSERT INTO t_specialty(sDeptNum,sNumber,sName,sTrainingObjective,sOccupationalDirection,sCertificate,sIntro) " +
                "VALUES(:deptNum,:number,:name,:trainingObjective,:occupationalDirection,:certificate,:intro)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(specialtyList.toArray());
        log.debug("批量保存导入的专业信息");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 获取系部专业的统计数量
     * @return List
     *
     * @author cx
     * @since 2014-12-01
     */
    public List<Map<String, Object>> getTotalForDept() {
        String sql = "SELECT sDeptNum AS deptNum,COUNT(id) AS total FROM t_specialty GROUP BY sDeptNum";

        log.debug("获取系部专业的统计数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource());
    }

    /**
     * 获取指定系部的专业信息
     * @param deptNum 系部编码
     * @return List
     *
     * @author cx
     * @since 2014-12-02
     */
    public List<SpecStatistics> getListByDeptNum(final String deptNum) {
        String sql = "SELECT id,sName FROM t_specialty WHERE sDeptNum=:deptNum";
        MapSqlParameterSource sps = new MapSqlParameterSource().addValue("deptNum", deptNum);

        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<SpecStatistics>() {
            @Override
            public SpecStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                SpecStatistics entity = new SpecStatistics();

                entity.setId(resultSet.getLong("id"));
                entity.setName(resultSet.getString("sName"));

                return entity;
            }
        });
    }

    /**
     * 获取与数据库中重复的专业编码
     * @param specNumSet 专业编码集合
     * @return String
     *
     * @author cx
     * @since 2014-12-04
     */
    public String getDuplicateNum (final Set<String> specNumSet) {
        if (specNumSet.isEmpty()) {
            log.debug("导入专业编码集合为0:specNumSet.isEmpty()");
            log.info("专业编号集合为0");
            return null;
        }

        try {
            String sql = "SELECT sNumber FROM t_specialty WHERE sNumber IN (:numSet) LIMIT 1";
            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", specNumSet);

            log.debug("获取导入的专业与表记录重复的编码");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("导入的专业与表记录无重复的编码");
            log.info("数据库中无重复的专业编码");
            return null;
        }
    }

    /**
     * 查询专业负责人负责的专业ID
     * @param leaderId 专业负责人ID
     * @return List
     *
     * @author cx
     * @since 2014-12-15
     */
    public List<Long> getIdsForLeader(final long leaderId) {
        String sql = "SELECT id FROM t_specialty WHERE iLeaderId=:leaderId";

        log.debug("查询专业负责人负责的专业ID");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("leaderId", leaderId), Long.class);
    }

    /**
     * 查询系部管理员负责的专业ID
     *
     * @author chenxin
     * @since 2015/1/14
     */
    public List<Long> getIdsForDeptLeader(final long deptLeaderId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT spec.id FROM t_teacher_role tr")
            .append(" JOIN t_department dept ON tr.iDeptId=dept.id AND tr.iTeacherId=:deptLeaderId AND tiRole=:role")
            .append(" JOIN t_specialty spec ON dept.sNumber=spec.sDeptNum");

        SqlParameterSource sps = new MapSqlParameterSource().addValue("deptLeaderId", deptLeaderId)
                .addValue("role", Roles.DEPT_ADMIN);

        log.debug("查询系部管理员负责的专业ID");

        return namedParameterJdbcTemplate.queryForList(sql.toString(), sps, Long.class);
    }

    /**
     * 查询系部管理员负责的专业
     *
     * @author chenxin
     * @since 2015/1/14
     */
    public List<Specialty> getAllForDeptLeader(final long deptLeaderId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT spec.id,spec.sName,spec.sNumber FROM t_teacher_role tr")
                .append(" JOIN t_department dept ON tr.iDeptId=dept.id AND tr.iTeacherId=:deptLeaderId AND tiRole=:role")
                .append(" JOIN t_specialty spec ON dept.sNumber=spec.sDeptNum");

        SqlParameterSource sps = new MapSqlParameterSource().addValue("deptLeaderId", deptLeaderId)
                .addValue("role", Roles.DEPT_ADMIN);

        log.debug("查询系部管理员负责的专业");

        return namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Specialty>() {
            @Override
            public Specialty mapRow(ResultSet rs, int rowNum) throws SQLException {
                Specialty entity = new Specialty();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setNumber(rs.getString("sNumber"));

                return entity;
            }
        });
    }

    /**
     * 判断用户是否是指定专业的专业负责人
     * @param leaderId 负责人编号
     * @param specialtyId 专业编号
     * @return Boolean
     *
     * @author farnuiping
     * @since 2014-12-24
     */
    public Boolean checkLeader(Long leaderId,Long specialtyId){
        String sql = "SELECT COUNT(id) FROM t_specialty WHERE iLeaderId=:leaderId AND id=:specialtyId LIMIT 1";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("leaderId",leaderId).addValue("specialtyId",specialtyId);

        log.debug("check user is specialty leader");

        return namedParameterJdbcTemplate.queryForObject(sql,params,Integer.class) == 1;
    }

    /**
     * ResultSet转换为专业实体
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    private RowMapper<Specialty> CreateMapper(){
        return new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Specialty entity = new Specialty();

                entity.setId(rs.getLong("id"));
                entity.setDeptNum(rs.getString("sDeptNum"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setLeaderId(rs.getLong("iLeaderId"));
                entity.setIntro(rs.getString("sIntro"));

                return entity;
            }
        };
    }

    /**
     * ResultSet转换为专业实体
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    private RowMapper<Specialty> CreateDetailMapper(){
        return new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Specialty entity = new Specialty();

                entity.setId(rs.getLong("id"));
                entity.setDeptNum(rs.getString("sDeptNum"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setLeaderId(rs.getLong("iLeaderId"));
                entity.setTrainingObjective(rs.getString("sTrainingObjective"));
                entity.setOccupationalDirection(rs.getString("sOccupationalDirection"));
                entity.setCertificate(rs.getString("sCertificate"));
                entity.setIntro(rs.getString("sIntro"));

                return entity;
            }
        };
    }

    /**
     * 专业习题统计(系统习题，我的习题)
     * @param deptCode 系部编码
     * @return
     * @author lyj
     * @since 2015-2-4
     */
    public List<Map<String,Object>> getSpecExercises(String deptCode, List<Long> specIds, int isOpen, int status, long creatorId, int tag, String search){
        StringBuilder sql = new StringBuilder("SELECT spec.id,spec.sName AS name,COUNT(exer.id) AS count FROM t_specialty spec LEFT JOIN t_exercise exer ON spec.id=exer.iSpecId");

        if (tag == 1 || tag == 3){
            log.debug("如果tag=1或tag=3：tag == 1 || tag == 3");
            sql = new StringBuilder("SELECT spec.id,spec.sName AS name,COUNT(exer.id) AS count FROM t_specialty spec JOIN t_exercise exer ON spec.id=exer.iSpecId");
        }

        MapSqlParameterSource param = new MapSqlParameterSource();

        //如果departCode等于0获取所有的专业习题列表，如果不等于零，根据系部编码查询对应的专业列表。
        if (!deptCode.equals("0")) {
            log.debug("如果departCode等于0获取所有的专业习题列表，如果不等于零，根据系部编码查询对应的专业列表:!deptCode.equals(\"0\")");
            sql.append(" AND spec.sDeptNum=:deptCode");
            param.addValue("deptCode", deptCode);
        }

        //如果专业ID为空，根据系部编码查询对应的专业列表;如果不为空，根据系部编码查询对应ID的专业列表
        if (specIds.size() > 0){
            log.debug("如果专业ID为空，根据系部编码查询对应的专业列表;如果不为空，根据系部编码查询对应ID的专业列表:specIds.size() > 0");
            sql.append(" AND spec.id IN (:iSpecIds)");
            param.addValue("iSpecIds", specIds);
        }

        //判断公开私有
        if (isOpen >= 0 && isOpen != 9){
            log.debug("判断公开私有:isOpen >= 0 && isOpen != 9");
            sql.append(" AND exer.tiOpen = :tiOpen");
            param.addValue("tiOpen", isOpen);
        }

        //判断习题状态
        if (status >= 0 && status != 9){
            log.debug("判断习题状态:status >= 0 && status != 9");
            sql.append(" AND exer.tiStatus = :tiStatus");
            param.addValue("tiStatus", status);
        }

        //判断创建者
        if (creatorId > 0){
            log.debug("判断创建者:creatorId > 0");
            if (tag == 3){
                log.debug("查询审批人：tag == 3");
                sql.append(" AND exer.iAuditorId = :iAuditorId");
                param.addValue("iAuditorId", creatorId);
            } else {
                log.debug("查询创建者");
                sql.append(" AND exer.iCreatorId = :iCreatorId");
                param.addValue("iCreatorId", creatorId);
            }
        }

        //根据关键字模糊搜索
        if (!search.isEmpty()){
            log.debug("根据关键字模糊搜索:!search.isEmpty()");
            sql.append(" AND UPPER(exer.sTopic) LIKE :search");
            param.addValue("search", "%" + search.toUpperCase() + "%");
        }

        sql.append(" GROUP BY spec.id ORDER BY count DESC");

        return  namedParameterJdbcTemplate.queryForList(sql.toString(),param);
    }

    /**
     * 根据id获取专业集合
     * @param specIds
     * @return
     * @author lyj
     * @since 2015-3-10
     */
    public List<Map<String, Object>> getSpecListById(List<Long> specIds){
        StringBuilder sql = new StringBuilder("SELECT id,sName AS name FROM t_specialty ");

        MapSqlParameterSource param = new MapSqlParameterSource();
        if (specIds.size() > 0){
            sql.append(" WHERE id IN (:id)");
            param.addValue("id", specIds);
        }

        log.debug("根据id获取专业集合");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), param);
    }


    //<editor-fold "专业群组">

    /**
     * 分页获取专业群组列表
     *
     * @param ids 专业ID集合
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @return page
     * @author lyj
     * @since 2015-5-6
     */
    public Page<Specialty> getSpecialtiesByIds(final List<Long> ids, final int pageNum, final int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT s.id,s.sNumber,s.sName AS speName,de.sName AS deName,te.sName AS teName FROM t_specialty s " +
                "LEFT JOIN t_department de ON s.sDeptNum = de.sNumber " +
                "JOIN t_teacher te ON s.iLeaderId = te.id " +
                "where s.id IN (:ids)");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(s.id) FROM t_specialty s " +
                "LEFT JOIN t_department de ON s.sDeptNum = de.sNumber " +
                "JOIN t_teacher te ON s.iLeaderId = te.id " +
                "where s.id IN (:ids)");

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("ids", ids);

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.toString(), param, Integer.class);

        if (total == 0) {
            log.debug("total == 0");
            return new Page<>();
        }

        //追加limit限制
        sql.append(getLimitStr(pageNum, pageSize));

        List<Specialty> specialtyList = namedParameterJdbcTemplate.query(sql.toString(), param, new RowMapper<Specialty>() {
            @Override
            public Specialty mapRow(ResultSet resultSet, int i) throws SQLException {
                Specialty specialty = new Specialty();

                specialty.setId(resultSet.getLong("id"));
                specialty.setNumber(resultSet.getString("sNumber"));
                specialty.setName(resultSet.getString("speName"));
                specialty.setDeptName(resultSet.getString("deName"));
                specialty.setTeacherName(resultSet.getString("teName"));

                return specialty;
            }
        });

        log.debug("分页获取专业群组列表");
        return new Page<>(total, pageSize, pageNum, specialtyList);
    }



    //<editor-fold "专业群组">

    /**
     * 获取与登录用户有关系的专业ID(没有专业负责人的过滤掉)
     *
     * @param ids
     * @return List<Long>
     * @author lyj
     * @since 2015-5-13
     */
    public List<Long> getSpecialtyIds(final Set<Long> ids) {
        StringBuilder sql = new StringBuilder("SELECT s.id FROM t_specialty s " +
                "JOIN t_teacher te ON s.iLeaderId = te.id ");

        if (ids.size() > 0) {
            sql.append("where s.id IN (:ids)");
            return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("ids", ids), Long.class);
        }

        return new ArrayList<>();

    }



    /**
     * 获取专业群组明细头部
     *
     * @param specId 专业ID
     * @return Specialty
     * @author lyj
     * @since 2015-5-6
     */
    public Specialty getSpecialtyTop(final long specId) {
        StringBuilder sql = new StringBuilder("SELECT s.sName AS speName,te.sName AS teName,te.sPicture FROM t_specialty s " +
                "LEFT JOIN t_teacher te ON s.iLeaderId = te.id " +
                "where s.id = :id");
        try {
            log.debug("获取专业群组明细头部");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("id", specId), new RowMapper<Specialty>() {
                @Override
                public Specialty mapRow(ResultSet resultSet, int i) throws SQLException {
                    Specialty specialty = new Specialty();

                    specialty.setName(resultSet.getString("speName"));
                    specialty.setPhoto(resultSet.getString("sPicture"));
                    specialty.setTeacherName(resultSet.getString("teName"));

                    return specialty;
                }
            });
        } catch (EmptyResultDataAccessException ex) {

            log.debug("获取专业群组明细头部失败");

            log.warn("获取专业群组明细头部失败！");
            return null;
        }
    }



    /**
     * 根据number、groupId判断登录用户是否属于该专业群组
     *
     * @param groupId 群组ID
     * @param number 工号/学号
     * @return int
     * @author lyj
     * @since 2015-5-13
     */
    public int checkGroupIdIsNum(final long groupId, final String number) {
        StringBuilder sql = new StringBuilder("SELECT" +
                "(SELECT COUNT(c.id) FROM t_class c WHERE c.id = :id AND c.sTeacherNum = :number) countClassGroup," +
                " COUNT(hc.id) AS countHClassGroup FROM t_hClass hc WHERE hc.id = :id AND hc.sTeacherNum = :number");


        Map<String, Object> map = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource()
                .addValue("id", groupId)
                .addValue("number", number), new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, Object> reMap = new HashMap<String, Object>();

                reMap.put("countClassGroup", resultSet.getInt("countClassGroup"));
                reMap.put("countHClassGroup", resultSet.getInt("countHClassGroup"));

                return reMap;
            }
        });

        return (int)map.get("countClassGroup") + (int)map.get("countHClassGroup");

    }

    //</editor-fold>

/**
     * 根据编码获取专业集合
     * @param numbers 专业编码
     * @return List
     * @author fanruiping
     * @since 2015-05-05
     */
    public Map<String, String> getNamesByNumbers(List<String> numbers){
        final Map<String, String> result = Maps.newHashMap();

        String sql = "SELECT sNumber,sName FROM t_specialty WHERE sNumber IN(:numbers)";

        namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("numbers", numbers), new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                result.put(rs.getString("sNumber"), rs.getString("sName"));

                return null;
            }
        });

        return result;
    }

    /**
     * 根据编号获取专业名称集合
     * @param ids 专业编号集合
     * @return List
     * @author fanruiping
     * @since 2015-05-05
     */
    public Map<Long, String> getNamesByIds(Set<Long> ids){
        final Map<Long, String> result = Maps.newHashMap();

        String sql = "SELECT id,sName FROM t_specialty WHERE id IN(:ids)";

        namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("ids", ids), new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                result.put(rs.getLong("id"), rs.getString("sName"));

                return null;
            }
        });

        return result;
    }

    /**
     * 获取所有专业编号集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-07
     */
    public List<Long> getAllIds(){
       String sql = "SELECT id FROM t_specialty";

        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource(), Long.class);
    }


    /**
     * 验证用户（userId）是否是专业（id)的负责人
     *
     * @param id 专业ID
     * @param userId 用户ID
     * @return int
     * @author lyj
     * @since 2015-5-11
     */
    public int checkByLeader(final long id, final long userId) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_specialty WHERE id = :id AND iLeaderId = :leaderId");

        log.debug("验证用户（userId）是否是专业（id)的负责人");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("id", id).addValue("leaderId", userId), Integer.class);
    }

    /**
     * 获取专业负责人负责的专业所属系部代码
     * @param userId 专业负责人编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-13
     */
    public List<String> getDeptNumByLeader(Long userId){
        String sql = "SELECT DISTINCT sDeptNum FROM t_specialty WHERE iLeaderId=:userId";

        return namedParameterJdbcTemplate.queryForList(sql,new MapSqlParameterSource().addValue("userId",userId),String.class);
    }

    /**
     * 根据专业负责人编号获取专业代码集合
     * @param leaderId 专业负责人编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-13
     */
    public List<String> getNumberByLeader(Long leaderId){
        String sql = "SELECT sNumber FROM t_specialty WHERE iLeaderId=:userId";

        return namedParameterJdbcTemplate.queryForList(sql,new MapSqlParameterSource().addValue("userId",leaderId), String.class);
    }
}
